/****** Object:  Stored Procedure [dbo].spChiTietPhieuNhapGetList Script Date: 6/12/2012 ******/
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'[dbo].[spBaoCaoTheoKho]') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
	DROP PROCEDURE [dbo].[spBaoCaoTheoKho]
GO

/******************************************************************************
**		Change History
*******************************************************************************
**		Date:		Author:					Description:
**		--------	--------				-----------------------------------
**		01/07/2012	SongTM					Innit created
*******************************************************************************/


CREATE PROCEDURE [dbo].[spBaoCaoTheoKho]
(
	@MaThuoc nvarchar(12),
	@SoLo nvarchar(12),
	@TuNgay datetime,
	@DenNgay datetime
)

WITH ENCRYPTION
AS

SELECT * FROM tbl_Congty
SELECT
	[tbl_ChiTietPhieuNhap].SoLuong As SoLuong,
	tbl_PhieuNhap.SoHD,
	tbl_PhieuNhap.NgayNhapKho as Ngay,
	tbl_NhaCungCap.TenNCC as DienGiai,
	tbl_LuuKho.*,
	[tbl_SanPham].*,
	tbl_DonViTinh.*
FROM
	[tbl_ChiTietPhieuNhap] INNER JOIN tbl_LuuKho ON tbl_LuuKho.MaLuuKho = [tbl_ChiTietPhieuNhap].MaLuuKho
	INNER JOIN tbl_SanPham ON [tbl_SanPham].Mathuoc = tbl_LuuKho.MaThuoc
	INNER JOIN tbl_PhieuNhap ON [tbl_PhieuNhap].SoPN = [tbl_ChiTietPhieuNhap].SoPN
	INNER JOIN tbl_NhaCungCap ON tbl_NhaCungCap.MaNCC = tbl_PhieuNhap.MaNCC
	INNER JOIN tbl_DonViTinh ON [tbl_DonViTinh].MaDonVi = [tbl_SanPham].MaDonVi
WHERE
	[tbl_SanPham].MaThuoc = @MaTHuoc 
	AND tbl_LuuKho.SoLo = @SoLo
UNION All
SELECT
	
	(0 - [Tbl_ChiTietPhieuXuat].SoLuong) As SoLuong,
	tbl_PhieuXuat.SoHD,
	tbl_PhieuXuat.NgayXuatKho as Ngay,
	tbl_KhachHang.TenKH as DienGiai,
	tbl_LuuKho.*,
	[tbl_SanPham].*,
	tbl_DonViTinh.*
FROM
	[Tbl_ChiTietPhieuXuat] INNER JOIN tbl_LuuKho ON tbl_LuuKho.MaLuuKho = [Tbl_ChiTietPhieuXuat].MaLuuKho
	INNER JOIN tbl_SanPham ON tbl_SanPham.MaThuoc = tbl_LuuKho.MaThuoc
	INNER JOIN tbl_DonViTinh ON tbl_SanPham.MaDonVi = tbl_DonViTinh.MaDonVi
	INNER JOIN tbl_PhieuXuat ON [tbl_PhieuXuat].SoPX = [Tbl_ChiTietPhieuXuat].SoPX
	INNER JOIN tbl_KhachHang ON tbl_KhachHang.MaKH = tbl_PhieuXuat.MaKH
WHERE
	tbl_LuuKho.MaThuoc = @MaTHuoc 
	AND tbl_LuuKho.SoLo = @SoLo
ORDER BY 3
GO

--GRANT EXECUTE ON [dbo].[spChiTietPhieuNhapGetList] TO [sa]
--GO